如何在 Postgres 8.2 中禁用參照完整性？ (How do I disable referential integrity in Postgres 8.2?)

問題描述

如何在 Postgres 8.2 中禁用參照完整性？ (How do I disable referential integrity in Postgres 8.2?)

Google 在這個上的結果有點薄，但表明這並不容易。

我的具體問題是我需要重新編號兩個相互關聯的表中的 ID，例如該表 B 中有一個“table_a_id”列。我不能先重新編號表 A，因為 B 中的子代指向舊 ID。我不能先重新編號表 B，因為它們會在創建之前指向新的 ID。現在重複三個或四個表。

當我可以“開始事務；禁用 ref 完整性；排序 ID；重新啟用 ref 完整性”時，我真的不想擺弄個人關係; 提交事務”。Mysql 和 MSSQL 都提供此功能 IIRC，所以如果 Postgres 沒有，我會感到驚訝。

參考解法

方法 1:

There are two things you can do (these are complementary, not alternatives):

• Create your foreign key constraints as DEFERRABLE. Then, call "SET CONSTRAINTS DEFERRED;", which will cause foreign key constraints not to be checked until the end of the transaction. Note that the default if you don't specify anything is NOT DEFERRABLE (annoyingly).
• Call "ALTER TABLE mytable DISABLE TRIGGER ALL;", which prevents any triggers executing while you load data, then "ALTER TABLE mytable ENABLE TRIGGER ALL;" when you're done to re‑enable them.

方法 2:

I found these 2 excellent scripts which generate the sql for dropping the constraints and then recreating them. here they are:

For dropping the constraints

SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" DROP CONSTRAINT "'||conname||'";'
FROM pg_constraint 
INNER JOIN pg_class ON conrelid=pg_class.oid 
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace 
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname

For recreating them

SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" ADD CONSTRAINT "'||conname||'" '|| pg_get_constraintdef(pg_constraint.oid)||';'
FROM pg_constraint
INNER JOIN pg_class ON conrelid=pg_class.oid
INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC;

Run these queries and the output will be the sql scripts that you need for dropping and creating the constraints.

Once you drop the constraints you can do all you like with the tables. When you are done re‑introduce them.

方法 3:

It does not seem possible. Other suggestions almost always refer to dropping the constraints and recreating them after work is done.

However, it seems you can make constraints DEFERRABLE, such that they are not checked until the end of a transaction. See PostgreSQL documentation for CREATE TABLE (search for 'deferrable', it's in the middle of the page).

方法 4:

I think you need to make a list of your foreign key constraints, drop them, do your changes, then add the constraints again. Check the documentation for alter table drop constraint and alter table add constraint.

方法 5:

Here's a Python script that will delete all constraints in a transaction, run some queries, then recreate all those constraints. pg_get_constraintdef makes this super‑easy:

class no_constraints(object):
    def __init__(self, connection):
        self.connection = connection

    def __enter__(self):
        self.transaction = self.connection.begin()
        try:
            self._drop_constraints()
        except:
            self.transaction.rollback()
            raise

    def __exit__(self, exc_type, exc_value, traceback):
        if exc_type is not None:
            self.transaction.rollback()
        else:
            try:
                self._create_constraints()
                self.transaction.commit()
            except:
                self.transaction.rollback()
                raise

    def _drop_constraints(self):
        self._constraints = self._all_constraints()

        for schemaname, tablename, name, def_ in self._constraints:
            self.connection.execute('ALTER TABLE "%s.%s" DROP CONSTRAINT %s' % (schemaname, tablename, name))

    def _create_constraints(self):
        for schemaname, tablename, name, def_ in self._constraints:
            self.connection.execute('ALTER TABLE "%s.%s" ADD CONSTRAINT %s %s' % (schamename, tablename, name, def_))

    def _all_constraints(self):
        return self.connection.execute("""
            SELECT n.nspname AS schemaname, c.relname, conname, pg_get_constraintdef(r.oid, false) as condef
                     FROM  pg_constraint r, pg_class c
                     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
                     WHERE r.contype = 'f'
                    and r.conrelid=c.oid
            """).fetchall()

if __name__ == '__main__':
    # example usage

    from sqlalchemy import create_engine

    engine = create_engine('postgresql://user:pass@host/dbname', echo=True)

    conn = engine.connect()
    with no_contraints(conn):
        r = conn.execute("delete from table1")
        print "%d rows affected" % r.rowcount
        r = conn.execute("delete from table2")
        print "%d rows affected" % r.rowcount

(by sanbikinoraion、Nick Johnson、Dimitris、Joel B Fant、Liam、zzzeek)

參考文件

1. How do I disable referential integrity in Postgres 8.2? (CC BY‑SA 2.5/3.0/4.0)